<?php
/* -------------------------------------------------------------------------------------
* 	ID:						$Id: orders_overview.php 225 2013-09-22 10:21:51Z phone.mueller@googlemail.com $
* 	Letzter Stand:			$Revision: 225 $
* 	zuletzt geaendert von:	$Author: siekiera $
* 	Datum:					$Date: 2013-09-22 10:21:51 +0000 (Sun, 22 Sep 2013) $
*
* 	SEO:mercari by Siekiera Media
* 	http://www.seo-mercari.de
*
* 	Copyright (c) since 2011 SEO:mercari
* --------------------------------------------------------------------------------------
* 	based on:
* 	(c) 2000-2001 The Exchange Project  (earlier name of osCommerce)
* 	(c) 2002-2003 osCommerce - www.oscommerce.com
* 	(c) 2003     nextcommerce - www.nextcommerce.org
* 	(c) 2005     xt:Commerce - www.xt-commerce.com
*
* 	Released under the GNU General Public License
* ----------------------------------------------------------------------------------- */

require('includes/application_top.php');
require(DIR_FS_CATALOG.DIR_WS_CLASSES.'class.price.php');
$price = new price($_SESSION['currency'], $_SESSION['customers_status']['customers_status_id'], $db);

if($_GET['bestell_nr'] == 'up') { // Bestellnummer
	$sortierung = 'order by o.orders_id ASC';
} elseif ($_GET['bestell_nr'] == 'down') {
	$sortierung = 'order by o.orders_id DESC';
} elseif ($_GET['name'] == 'up') { // Name
	$sortierung = 'order by o.customers_name ASC';
} elseif ($_GET['name'] == 'down') {
	$sortierung = 'order by o.customers_name DESC';
} elseif ($_GET['land'] == 'up') { // Land
	$sortierung = 'order by o.customers_country ASC';
} elseif ($_GET['land'] == 'down') {
	$sortierung = 'order by o.customers_country DESC';
} elseif ($_GET['datum'] == 'up') { // Datum
	$sortierung = 'order by o.date_purchased ASC';
} elseif ($_GET['datum'] == 'down') {
	$sortierung = 'order by o.date_purchased DESC';
} elseif ($_GET['menge'] == 'up') { // Artikelmenge
	$sortierung = 'order by o.customers_name ASC';
} elseif ($_GET['menge'] == 'down') {
	$sortierung = 'order by o.customers_name DESC';
} elseif ($_GET['zahlart'] == 'up') { // Zahlart
	$sortierung = 'order by o.payment_method ASC';
} elseif ($_GET['zahlart'] == 'down') {
	$sortierung = 'order by o.payment_method DESC';
} elseif ($_GET['brutto'] == 'up') { // Brutto
	$sortierung = 'order by o.customers_name ASC';
} elseif ($_GET['brutto'] == 'down') {
	$sortierung = 'order by o.customers_name DESC';
} elseif ($_GET['tax'] == 'up') { // UST
	$sortierung = 'order by o.customers_name ASC';
} elseif ($_GET['tax'] == 'down') {
	$sortierung = 'order by o.customers_name DESC';
} elseif ($_GET['netto'] == 'up') { // Netto
	$sortierung = 'order by o.customers_name ASC';
} elseif ($_GET['netto'] == 'down') {
	$sortierung = 'order by o.customers_name DESC';
}elseif ($_GET['versandart'] == 'up') {
	$sortierung = 'order by o.shipping_method ASC';
}elseif ($_GET['versandart'] == 'down') {
	$sortierung = 'order by o.shipping_method DESC';
}elseif ($_GET['status'] == 'up') {
	$sortierung = 'order by o.orders_status ASC';
}elseif ($_GET['status'] == 'down') {
	$sortierung = 'order by o.orders_status DESC';
} else { // Default
	$sortierung = 'order by o.orders_id ASC';
}

$months = array('1' => '01','2' => '02','3' => '03','4' => '04','5' => '05','6' => '06','7' => '07','8' => '08','9' => '09','10' => '10','11' => '11','12' => '12');

if(($_GET['monat'] == '') || ($_GET['monat'] == '0'))
	$monat = date("m");
else
	$monat = $months[$_GET['monat']];

if(($_GET['jahr'] == '') || ($_GET['jahr'] == '0'))
	$jahr = date("Y");
else
	$jahr = (int)$_GET['jahr'];

$orders_overview_query = "SELECT
								o.orders_id,
								o.orders_status,
								o.customers_country,
								o.afterbuy_success,
								o.afterbuy_id,
								o.customers_name,
								o.customers_id,
								o.customers_cid,
								o.delivery_country_iso_code_2,
								o.payment_method,
								o.date_purchased,
								o.last_modified,
								o.shipping_method,
								o.currency,
								o.currency_value,
								s.orders_status_name,
								ot.text as order_total
							FROM
								".TABLE_ORDERS." o
								LEFT JOIN ".TABLE_ORDERS_TOTAL." ot
									ON (o.orders_id = ot.orders_id),
								".TABLE_ORDERS_STATUS." s
							WHERE
								o.date_purchased LIKE '". $jahr."-".$monat."%'
							AND
								(o.orders_status = s.orders_status_id 
								AND 
									s.language_id = '".$_SESSION['languages_id']."' 
								AND 
									ot.class = 'ot_total')
								OR (o.orders_status = '0' 
									AND 
										ot.class = 'ot_total'
									AND 
										s.orders_status_id = '1' 
									AND 
										s.language_id = '".$_SESSION['languages_id']."')".$sortierung."";

$query = page_break_create($_POST['per_site'], $_GET['page'], $orders_overview_query, 'orders_overview.php');

$gesamtsumme_brutto = $db->db_query("SELECT sum(ot.value) AS brutto FROM ".TABLE_ORDERS." o, ".TABLE_ORDERS_TOTAL." ot WHERE o.date_purchased LIKE '". $jahr."-".$monat."%' AND ot.orders_id = o.orders_id AND ot.class = 'ot_total' ");

$gesamtsumme_tax = $db->db_query("SELECT sum(ot.value) AS tax FROM ".TABLE_ORDERS." o, ".TABLE_ORDERS_TOTAL." ot WHERE o.date_purchased LIKE '". $jahr."-".$monat."%' AND ot.orders_id = o.orders_id AND ot.class = 'ot_tax' ");

$gesamtsumme_netto = $price->format($gesamtsumme_brutto->fields['brutto'] - $gesamtsumme_tax->fields['tax'], true);

require(DIR_WS_INCLUDES.'metatag.php');
?>
<style type="text/css">
	a:focus {outline: none}
	table.overview td a {text-decoration: underline}
	table.overview td.td_bg_dunkel {background: #F9F0F1}
	table.overview td.td_bg_hell {border-bottom: 1px solid #F9F0F1}
</style>
</head>
<body>
<?php require(DIR_WS_INCLUDES.'header.php'); ?>
<div id="wrapper">
	<table class="outerTable" cellpadding="0" cellspacing="0">
	<tr>
		<td class="columnLeft2" width="<?php echo BOX_WIDTH; ?>" valign="top">
			<?php require(DIR_WS_INCLUDES.'column_left.php'); ?>
		</td>
		<td valign="top" class="boxCenter">
		<?php
			$monate_namen = array('1' => 'Januar','2' => 'Februar','3' => 'M&auml;rz','4' => 'April','5' => 'Mai','6' => 'Juni','7' => 'Juli','8' => 'August','9' => 'September','10' => 'Oktober','11' => 'November','12' => 'Dezember');
			echo draw_form('SELECT', 'orders_overview.php', '', 'GET', '');

				$erste_bestellung = $db->db_query("SELECT date_purchased FROM ".TABLE_ORDERS." ORDER BY date_purchased ASC LIMIT 1");
				if($erste_bestellung->_numOfRows) {
					if($_GET['monat'] > 0)
						$dropdown_monat = '<select name="monat" onchange="javascript:this.form.submit();">';
					else
						$dropdown_monat = '<select name="monat" onchange="javascript:this.form.submit();"><option selected="" value="0">w&auml;hlen Sie einen Monat</option>';

					for($monate = 1;  $monate <= 12; $monate++)
						$dropdown_monat .= '<option value="'.$monate.'"'.($monate == (int)$_GET['monat'] ? 'selected=""' : '').'>'.$monate_namen[$monate].'</option>';

					$dropdown_monat .= '</select>';

					if($_GET['jahr'] > 0)
						$dropdown_jahr = '<select name="jahr" onchange="javascript:this.form.submit();">';
					else
						$dropdown_jahr = '<select name="jahr" onchange="javascript:this.form.submit();"><option selected="" value="0">w&auml;hlen Sie einen Jahr</option>';

					$erstes_jahr = substr($erste_bestellung->fields['date_purchased'],0,4);
					$dieses_jahr = date('Y');
					for($jahre = $erstes_jahr;  $jahre <= $dieses_jahr; $jahre++)
						$dropdown_jahr .= '<option value="'.$jahre.'"'.($jahre == (int)$_GET['jahr'] ? 'selected=""' : '').'>'.$jahre.'</option>';

					$dropdown_jahr .= '</select>';
					$value = $monat.'/'.$jahr.' Drucken';
					
					$print_button = '<button class="button" onclick="window.open(\'orders_overview_print.php?action=print&monat='.$monat.'&jahr='.$jahr.'\', \'popup\', \'toolbar=0,scrollbars=yes, width=740, height=680\')">'.$value.'</button>';
				}

			?>
			<table class="table_pageHeading" border="0" cellspacing="0" cellpadding="0">
				<tr>
					<td class="pageHeading"><?php echo 'Bestell&uuml;bersicht - '.$monat.'/'.$jahr; ?></td>
				</tr>
			</table>
			<table cellpadding="5" cellspacing="0" width="100%">
				<tr>
					<td width="1">
						<?php echo $dropdown_monat; ?>
					</td>
					<td width="1">
						<?php echo $dropdown_jahr; ?>
					</td>
					<td>
						<?php echo $print_button; ?>
					</td>
				</tr>
			</table>
		</form>
		<?php echo draw_form('sort', 'orders_overview.php', '', 'GET', ''); ?>
			<input type="hidden" value="<?php echo $_GET['jahr']; ?>" name="jahr" />
			<input type="hidden" value="<?php echo $_GET['monat']; ?>" name="monat" />
			<table width="100%" cellspacing="0" cellpadding="0" class="dataTable">
				<tr class="dataTableHeadingRow"><?php
					if($_GET['jahr'])
						$get_jahr = '&jahr='.$_GET['jahr'];
					if($_GET['monat'])
						$get_monat = '&monat='.$_GET['monat'];
					?>
					<td class="dataTableHeadingContent" align="center">
						Best-Nr<br/>
						<a href="<?php echo basename($_SERVER['PHP_SELF']).'?bestell_nr=up'.$get_monat.$get_jahr; ?>">
							<img src="images/up.gif" alt="" title="" />
						</a>
						<a href="<?php echo basename($_SERVER['PHP_SELF']).'?bestell_nr=down'.$get_monat.$get_jahr; ?>">
							<img src="images/down.gif" alt="" title="" />
						</a>
					</td>
					<td class="dataTableHeadingContent">
						Kundenname<br/>
						<a href="<?php echo basename($_SERVER['PHP_SELF']).'?name=up'.$get_monat.$get_jahr; ?>">
							<img src="images/up.gif" alt="" title="" />
						</a>
						<a href="<?php echo basename($_SERVER['PHP_SELF']).'?name=down'.$get_monat.$get_jahr; ?>">
							<img src="images/down.gif" alt="" title="" />
						</a>
					</td>
					<td class="dataTableHeadingContent" align="center">
						Land<br/>
						<a href="<?php echo basename($_SERVER['PHP_SELF']).'?land=up'.$get_monat.$get_jahr; ?>">
							<img src="images/up.gif" alt="" title="" />
						</a>
						<a href="<?php echo basename($_SERVER['PHP_SELF']).'?land=down'.$get_monat.$get_jahr; ?>">
							<img src="images/down.gif" alt="" title="" />
						</a>
					</td>
					<td class="dataTableHeadingContent" align="center">
						Best-Datum<br/>
						<a href="<?php echo basename($_SERVER['PHP_SELF']).'?datum=up'.$get_monat.$get_jahr; ?>">
							<img src="images/up.gif" alt="" title="" />
						</a>
						<a href="<?php echo basename($_SERVER['PHP_SELF']).'?datum=down'.$get_monat.$get_jahr; ?>">
							<img src="images/down.gif" alt="" title="" />
						</a>
					</td>
					<td class="dataTableHeadingContent" align="center">
						Artikel ?
					</td>
					<td class="dataTableHeadingContent" align="center">
						Zahlart<br/>
						<a href="<?php echo basename($_SERVER['PHP_SELF']).'?zahlart=up'.$get_monat.$get_jahr; ?>">
							<img src="images/up.gif" alt="" title="" />
						</a>
						<a href="<?php echo basename($_SERVER['PHP_SELF']).'?zahlart=down'.$get_monat.$get_jahr; ?>">
							<img src="images/down.gif" alt="" title="" />
						</a>
					</td>
					<td class="dataTableHeadingContent" align="center">
						Versandart<br/>
						<a href="<?php echo basename($_SERVER['PHP_SELF']).'?versandart=up'.$get_monat.$get_jahr; ?>">
							<img src="images/up.gif" alt="" title="" />
						</a>
						<a href="<?php echo basename($_SERVER['PHP_SELF']).'?versandart=down'.$get_monat.$get_jahr; ?>">
							<img src="images/down.gif" alt="" title="" />
						</a>
					</td>
					<td class="dataTableHeadingContent" align="center">
						Status<br/>
						<a href="<?php echo basename($_SERVER['PHP_SELF']).'?status=up'.$get_monat.$get_jahr; ?>">
							<img src="images/up.gif" alt="" title="" />
						</a>
						<a href="<?php echo basename($_SERVER['PHP_SELF']).'?status=down'.$get_monat.$get_jahr; ?>">
							<img src="images/down.gif" alt="" title="" />
						</a>
					</td>
					<td class="dataTableHeadingContent" align="right">
						Brutto
					</td>
					<td class="dataTableHeadingContent" align="right">
						davon UST
					</td>
					<td class="dataTableHeadingContent" align="right">
						Netto
					</td>
				</tr>
				<?php
					$orders_overview = $query['query'];
					if(!$orders_overview->_numOfRows)
						echo '<tr><td colspan="11" align="center" class="main"><br /><strong>Es wurden keine Ums&auml;tze gefunden.</strong><br /><br /></td></tr>';
					else {
						$i = 1;
						while(!$orders_overview->EOF) {
							$menge = $db->db_query("SELECT products_id FROM ".TABLE_ORDERS_PRODUCTS." WHERE orders_id = '".$orders_overview->fields['orders_id']."'");
							
							if(!empty($orders_overview->fields['payment_method'])) {
								include (DIR_FS_LANGUAGES.$_SESSION['language'].'/modules/payment/'.$orders_overview->fields['payment_method'].'.php');
								$payment_method = constant(strtoupper('MODULE_PAYMENT_'.$orders_overview->fields['payment_method'].'_TEXT_TITLE'));
							} else
								$payment_method = ' - ';

							$order_status = $db->db_query("SELECT orders_status_name FROM ".TABLE_ORDERS_STATUS." WHERE orders_status_id = '".$orders_overview->fields['orders_status']."' AND language_id = '".(int)$_SESSION['languages_id']."'");

							$orders_brutto = $db->db_query("SELECT text, value FROM ".TABLE_ORDERS_TOTAL." WHERE orders_id = '".$orders_overview->fields['orders_id']."' AND class = 'ot_total' ");

							$orders_tax = $db->db_query("SELECT text, value FROM ".TABLE_ORDERS_TOTAL." WHERE orders_id = '".$orders_overview->fields['orders_id']."' AND class = 'ot_tax' ");

							$orders_netto = number_format($orders_brutto->fields['value'] - $orders_tax->fields['value'],2);

							if($orders_tax->fields['text'] == '')
								$tax = '-';
							else
								$tax = $price->format($orders_tax->fields['value'], true);

							if($i == '2') {
								$class = ' class="td_bg_hell smallText"';
								$i = 0;
							}
							else
								$class = ' class="td_bg_dunkel smallText"';

							echo '<tr>';
								echo '<td'.$class.' align="center" class="smallText"><a href="'.href_link('orders.php','oID='.$orders_overview->fields['orders_id'].'&action=edit').'">'.$orders_overview->fields['orders_id'].'</a></td>';
								echo '<td'.$class.'><a href="'.href_link('customers.php','cID='.$orders_overview->fields['customers_id'].'&action=edit').'">'.$orders_overview->fields['customers_name'].'</a></td>';
								echo '<td align="center"'.$class.'>'.$orders_overview->fields['delivery_country_iso_code_2'].'</td>';
								echo '<td align="center"'.$class.'>'.$orders_overview->fields['date_purchased'].'</td>';
								echo '<td align="center"'.$class.'>'.$menge->_numOfRows.'x</td>';
								echo '<td align="center"'.$class.'>'.$payment_method.'</td>';
								echo '<td align="center"'.$class.'>'.$orders_overview->fields['shipping_method'].'</td>';
								echo '<td align="center"'.$class.'>'.$order_status->fields['orders_status_name'].'</td>';
								echo '<td align="right"'.$class.'>'.$price->format(strip_tags($orders_brutto->fields['value']), true).'</td>';
								echo '<td align="right"'.$class.'>'.$tax.'</td>';
								echo '<td align="right"'.$class.'><strong>'.$price->format($orders_netto, true).'</strong></td>';
							echo '</tr>';

							$i++;
							$orders_overview->MoveNext();
						}
						?>
						<tr>
							<td colspan="8" align="right"  class="main">
								Gesamt:
							</td>
							<td align="right"  class="main">
								<strong><nobr><?php echo $price->format($gesamtsumme_brutto->fields['brutto'],true); ?></nobr></strong>
							</td>
							<td align="right"  class="main">
								<strong><nobr><?php echo $price->format($gesamtsumme_tax->fields['tax'], true); ?></nobr></strong>
							</td>
							<td  class="main" align="right">
								<strong><nobr><?php echo $gesamtsumme_netto; ?></nobr></strong>
							</td>
						</tr>
						<?php } ?>
				</table>
			</form><br />
			<table border="0" cellspacing="10" cellpadding="4" width="100%">
			 <tr class="page_break">
				<td class="smallText" valign="top" width="33.3%"><?php echo 'Angezeigt werden '.$query['from'].' bis '.$query['to'].' (von insgesamt '.$query['total'].' Bestellungen)'; ?></td>
				<td class="smallText" align="center" width="33.3%"></td>
				<td class="smallText" align="right" width="33.3%">
					Produkte pro Seite: <?php echo $query['page_break']->perSiteDropdown($_GET['page']); ?>
				</td>
			</tr>
			<tr><td align="center" colspan="3"><?php echo '<br />'.$query['links']; ?></td></tr>
			</table>
		</td>
	</tr>
</table>
<?php require(DIR_WS_INCLUDES.'footer.php'); ?>
</body>
</html>
<?php require(DIR_WS_INCLUDES.'application_bottom.php'); ?>